将 Excel 数据输出为 JSON |
您所在的位置：网站首页 › excel 导入json › 将 Excel 数据输出为 JSON |
|
将 Excel 表数据输出为 JSON，以便在 Power Automate 中使用
项目
03/30/2023
Excel 表数据可以表示为 JSON 形式的对象数组。 每个 对象表示表中的一行。 这有助于以用户可见的一致格式从 Excel 中提取数据。 然后，可以通过 Power Automate 流将数据提供给其他系统。 示例 Excel 文件下载文件 table-data-with-hyperlinks.xlsx 即可使用工作簿。
此示例的变体还包括其中一个表列中的超链接。 这允许在 JSON 中显示其他级别的单元格数据。 示例代码：以 JSON 的形式返回表数据 添加以下脚本以亲自试用示例！ 注意 可以更改结构以 interface TableData 匹配表列。 请注意，对于带空格的列名，请务必将键放在引号中，如示例中的 。"Event ID" 有关使用 JSON 的详细信息，请阅读 使用 JSON 将数据传入 Office 脚本和从 Office 脚本传递数据。 function main(workbook: ExcelScript.Workbook): TableData[] { // Get the first table in the "PlainTable" worksheet. // If you know the table name, use `workbook.getTable('TableName')` instead. const table = workbook.getWorksheet('PlainTable').getTables()[0]; // Get all the values from the table as text. const texts = table.getRange().getTexts(); // Create an array of JSON objects that match the row structure. let returnObjects: TableData[] = []; if (table.getRowCount() > 0) { returnObjects = returnObjectFromValues(texts); } // Log the information and return it for a Power Automate flow. console.log(JSON.stringify(returnObjects)); return returnObjects } // This function converts a 2D array of values into a generic JSON object. // In this case, we have defined the TableData object, but any similar interface would work. function returnObjectFromValues(values: string[][]): TableData[] { let objectArray: TableData[] = []; let objectKeys: string[] = []; for (let i = 0; i < values.length; i++) { if (i === 0) { objectKeys = values[i] continue; } let object: {[key: string]: string} = {} for (let j = 0; j < values[i].length; j++) { object[objectKeys[j]] = values[i][j] } objectArray.push(object as unknown as TableData); } return objectArray; } interface TableData { "Event ID": string Date: string Location: string Capacity: string Speakers: string } “PlainTable”工作表的示例输出 [{ "Event ID": "E107", "Date": "2020-12-10", "Location": "Montgomery", "Capacity": "10", "Speakers": "Debra Berger" }, { "Event ID": "E108", "Date": "2020-12-11", "Location": "Montgomery", "Capacity": "10", "Speakers": "Delia Dennis" }, { "Event ID": "E109", "Date": "2020-12-12", "Location": "Montgomery", "Capacity": "10", "Speakers": "Diego Siciliani" }, { "Event ID": "E110", "Date": "2020-12-13", "Location": "Boise", "Capacity": "25", "Speakers": "Gerhart Moller" }, { "Event ID": "E111", "Date": "2020-12-14", "Location": "Salt Lake City", "Capacity": "20", "Speakers": "Grady Archie" }, { "Event ID": "E112", "Date": "2020-12-15", "Location": "Fremont", "Capacity": "25", "Speakers": "Irvin Sayers" }, { "Event ID": "E113", "Date": "2020-12-16", "Location": "Salt Lake City", "Capacity": "20", "Speakers": "Isaiah Langer" }, { "Event ID": "E114", "Date": "2020-12-17", "Location": "Salt Lake City", "Capacity": "20", "Speakers": "Johanna Lorenz" }] 示例代码：使用超链接文本以 JSON 的形式返回表数据注意 该脚本始终从第 4 列提取超链接， (0 索引) 表。 可以通过修改注释下的代码来更改该顺序或包含多个列作为超链接数据 // For the 4th column (0 index), extract the hyperlink and use that instead of text. function main(workbook: ExcelScript.Workbook): TableData[] { // Get the first table in the "WithHyperLink" worksheet. // If you know the table name, use `workbook.getTable('TableName')` instead. const table = workbook.getWorksheet('WithHyperLink').getTables()[0]; // Get all the values from the table as text. const range = table.getRange(); // Create an array of JSON objects that match the row structure. let returnObjects: TableData[] = []; if (table.getRowCount() > 0) { returnObjects = returnObjectFromValues(range); } // Log the information and return it for a Power Automate flow. console.log(JSON.stringify(returnObjects)); return returnObjects } function returnObjectFromValues(range: ExcelScript.Range): TableData[] { let values = range.getTexts(); let objectArray : TableData[] = []; let objectKeys: string[] = []; for (let i = 0; i < values.length; i++) { if (i === 0) { objectKeys = values[i] continue; } let object = {} for (let j = 0; j < values[i].length; j++) { // For the 4th column (0 index), extract the hyperlink and use that instead of text. if (j === 4) { object[objectKeys[j]] = range.getCell(i, j).getHyperlink().address; } else { object[objectKeys[j]] = values[i][j]; } } objectArray.push(object as TableData); } return objectArray; } interface TableData { "Event ID": string Date: string Location: string Capacity: string "Search link": string Speakers: string } “WithHyperLink”工作表的示例输出 [{ "Event ID": "E107", "Date": "2020-12-10", "Location": "Montgomery", "Capacity": "10", "Search link": "https://www.google.com/search?q=Montgomery", "Speakers": "Debra Berger" }, { "Event ID": "E108", "Date": "2020-12-11", "Location": "Montgomery", "Capacity": "10", "Search link": "https://www.google.com/search?q=Montgomery", "Speakers": "Delia Dennis" }, { "Event ID": "E109", "Date": "2020-12-12", "Location": "Montgomery", "Capacity": "10", "Search link": "https://www.google.com/search?q=Montgomery", "Speakers": "Diego Siciliani" }, { "Event ID": "E110", "Date": "2020-12-13", "Location": "Boise", "Capacity": "25", "Search link": "https://www.google.com/search?q=Boise", "Speakers": "Gerhart Moller" }, { "Event ID": "E111", "Date": "2020-12-14", "Location": "Salt Lake City", "Capacity": "20", "Search link": "https://www.google.com/search?q=salt+lake+city", "Speakers": "Grady Archie" }, { "Event ID": "E112", "Date": "2020-12-15", "Location": "Fremont", "Capacity": "25", "Search link": "https://www.google.com/search?q=Fremont", "Speakers": "Irvin Sayers" }, { "Event ID": "E113", "Date": "2020-12-16", "Location": "Salt Lake City", "Capacity": "20", "Search link": "https://www.google.com/search?q=salt+lake+city", "Speakers": "Isaiah Langer" }, { "Event ID": "E114", "Date": "2020-12-17", "Location": "Salt Lake City", "Capacity": "20", "Search link": "https://www.google.com/search?q=salt+lake+city", "Speakers": "Johanna Lorenz" }] 在 Power Automate 中使用有关如何在 Power Automate 中使用此类脚本，请参阅 使用 Power Automate 创建自动化工作流。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |